IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetResearchFinanceInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetResearchFinanceInfo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
        
CREATE PROCEDURE [dbo].[GetResearchFinanceInfo]        
AS        
BEGIN        
 SET NOCOUNT ON        
        
 SELECT RFI.Id,
		RFI.FwkDomainOrganizationId,
		FDO.InstitutionId, 
		FDO.Institution,         
        FDO.ChiefCode,
        RFI .RoleID as 'RoleId',
        RT.NAME as 'Role', 
       RFI.AdmSponsorTypeId as 'AdmSponsorTypeId', 
       AST.[Name] as AdmSponsorTypeName,         
       RFI.AdmPersonId, AP.LastName + ', ' + AP.FirstName + ' (' + FDU.UserName + ')' as FullName,        
  (Select top 1 ATN.Number from dbo.AdmTelephoneNumber ATN WHERE AdmTelephoneTypeId = 1 AND ATN.AdmPersonId = AP.Id) as Phone        
 FROM dbo.AdmResearchFinanceInfo RFI        
  INNER JOIN dbo.FwkDomainOrganization FDO ON FDO.Id = RFI.FwkDomainOrganizationId        
  INNER JOIN dbo.AdmSponsorType AST ON AST.Id = RFI.AdmSponsorTypeId        
  INNER JOIN dbo.AdmPerson AP ON AP.Id = RFI.AdmPersonId        
  INNER JOIN dbo.FwkDomainUser FDU ON AP.FwkDomainUserId = FDU.Id     
  INNER JOIN dbo.admRoleTypes RT ON RT.RoleId = RFI.RoleId
       
          
 SET NOCOUNT OFF        
END 
GO
